package com.reserv.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.NamingException;

import com.reserv.db.ConnectionProvider;
import com.reserv.db.JDBCManager;
import com.reserv.util.SearchVar;
import com.reserv.vo.ServiceVO;

public class ServiceDAO implements DAO<ServiceVO, Integer> {
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;

	private static ServiceDAO instance = new ServiceDAO();
	public static ServiceDAO getInstance() {
		return instance != null ? instance : new ServiceDAO();
	}
	@Override	//서비스 등록
	public void createVO(ServiceVO service) { }
	@Override	//서비스 정보 가져오기
	public ServiceVO getVO(Integer sno) { 
		String sql = "SELECT * FROM service WHERE sno=?";
		ServiceVO s = null;
		try {
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql);

			pstmt.setInt(1, sno);

			rs = pstmt.executeQuery();
			while(rs.next()) {
				s = new ServiceVO();

				s.setSno(rs.getInt(1));
				s.setFno(rs.getInt(2));
				s.setType(rs.getString(3));
				s.setKind(rs.getString(4));
				s.setLocal(rs.getString(5));
				s.setTitle(rs.getString(6));
				s.setImg(rs.getString(7));
				s.setPhone(rs.getString(8));
				s.setFee(rs.getInt(9));
				s.setDetail(rs.getString(10));
				s.setRegDate(rs.getTimestamp(11));
				s.setRtsDate(rs.getTimestamp(12));
				s.setRteDate(rs.getTimestamp(13));
				s.setRsDate(rs.getTimestamp(14));
				s.setReDate(rs.getTimestamp(15));
				s.setRcDate(rs.getInt(16));
				s.setStat(rs.getInt(17));
				s.setMaxrv(rs.getInt(18));
				s.setStartpt(rs.getInt(19));

			}
		} catch(SQLException | NamingException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return s; 
	}
	@Override	//서비스 리스트 가져오기
	public List<ServiceVO> getListVOs() { return null; }
	@Override	//서비스 정보 수정
	public void updateVO(ServiceVO service) { }
	@Override	//서비스 삭제
	public void deleteVO(Integer serviceNo) { }

	//get Count
	public int getServiceCount(String type, SearchVar sv) {
		StringBuilder sb = new StringBuilder();

		if(sv.isHasRt()) {
			String sql = " and rtsDate <= '" + sv.getRteDate() + "' and rteDate >= '" + sv.getRtsDate() + "'";
			sb.append(sql);
		}
		if(sv.isHasR()) {
			String sql = " and rsDate <= '" + sv.getReDate() + "' and reDate >= '" + sv.getRsDate() + "'";
			sb.append(sql);
		}
		if(sv.isHasLocal()) {
			String sql = " and local = '" + sv.getLocal() + "'";
			sb.append(sql);
		}
		if(sv.isHasKind()) {
			String sql = " and kind = '" + sv.getKind() + "'";
			sb.append(sql);
		}

		String sql = "SELECT COUNT(*) FROM service WHERE type=?" + sb.toString();
		
		int count = 0;
		try {
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, type);

			rs = pstmt.executeQuery();
			while(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException | NamingException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return count;
	}

	//get paging list
	public List<ServiceVO> getListVOs(String type, int startRow, int pageSize, SearchVar sv) {
		StringBuilder sb = new StringBuilder();

		if(sv.isHasRt()) {
			String sql = " and rtsDate <= '" + sv.getRteDate() + "' and rteDate >= '" + sv.getRtsDate() + "'";
			sb.append(sql);
		}
		if(sv.isHasR()) {
			String sql = " and rsDate <= '" + sv.getReDate() + "' and reDate >= '" + sv.getRsDate() + "'";
			sb.append(sql);
		}
		if(sv.isHasLocal()) {
			String sql = " and local = '" + sv.getLocal() + "'";
			sb.append(sql);
		}
		if(sv.isHasKind()) {
			String sql = " and kind = '" + sv.getKind() + "'";
			sb.append(sql);
		}

		String sql = "SELECT * FROM service WHERE type=?" + sb.toString() + " LIMIT ?, ?";

		List<ServiceVO> list = null;

		try{
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, type);
			pstmt.setInt(2, startRow);
			pstmt.setInt(3, pageSize);

			rs = pstmt.executeQuery();
			if(rs.next()) {
				list = new ArrayList<>();
				do {
					ServiceVO s = new ServiceVO();

					s.setSno(rs.getInt(1));
					s.setFno(rs.getInt(2));
					s.setType(rs.getString(3));
					s.setKind(rs.getString(4));
					s.setLocal(rs.getString(5));
					s.setTitle(rs.getString(6));
					s.setImg(rs.getString(7));
					s.setPhone(rs.getString(8));
					s.setFee(rs.getInt(9));
					s.setDetail(rs.getString(10));
					s.setRegDate(rs.getTimestamp(11));
					s.setRtsDate(rs.getTimestamp(12));
					s.setRteDate(rs.getTimestamp(13));
					s.setRsDate(rs.getTimestamp(14));
					s.setReDate(rs.getTimestamp(15));
					s.setRcDate(rs.getInt(16));
					s.setStat(rs.getInt(17));
					s.setMaxrv(rs.getInt(18));
					s.setStartpt(rs.getInt(19));


					list.add(s);
				} while(rs.next());
			}
		} catch(SQLException | NamingException e) {
			e.printStackTrace();

		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	public List<String> getKindList() {
		String Sql = "SELECT DISTINCT kind FROM service";
		List<String> list = null;

		try{
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(Sql);

			rs = pstmt.executeQuery();

			if(rs.next()) {
				list = new ArrayList<>();
				do {
					list.add(rs.getString(1));
				} while(rs.next());
			}
		} catch(SQLException | NamingException e) {
			e.printStackTrace();

		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	public List<String> getLocalList() {
		String Sql = "SELECT DISTINCT local FROM service";
		List<String> list = null;

		try{
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(Sql);

			rs = pstmt.executeQuery();

			if(rs.next()) {
				list = new ArrayList<>();
				do {
					list.add(rs.getString(1));
				} while(rs.next());
			}
		} catch(SQLException | NamingException e) {
			e.printStackTrace();

		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	public List<String> getLocalList(String type) {
		String Sql = "SELECT DISTINCT local FROM service WHERE type=?";
		List<String> list = null;

		try{
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(Sql);

			pstmt.setString(1, type);
			
			rs = pstmt.executeQuery();

			if(rs.next()) {
				list = new ArrayList<>();
				do {
					list.add(rs.getString(1));
				} while(rs.next());
			}
		} catch(SQLException | NamingException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	
	public List<String> getTypeList() {
		String Sql = "SELECT DISTINCT type FROM service";
		List<String> list = null;

		try{
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(Sql);

			rs = pstmt.executeQuery();

			if(rs.next()) {
				list = new ArrayList<>();
				do {
					String type = rs.getString(1);
					if(type.equals("gym")) {
						list.add("체육시설");
					} else if(type.equals("pub")) {
						list.add("시설대관");
					}
				} while(rs.next());
			}
		} catch(SQLException | NamingException e) {
			e.printStackTrace();

		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	
	public List<String> getKindList(String type, String local) {
		String Sql = "SELECT DISTINCT kind FROM service WHERE type=? AND local=?";
		List<String> list = null;

		try{
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(Sql);

			pstmt.setString(1, type);
			pstmt.setString(2, local);
			
			rs = pstmt.executeQuery();

			if(rs.next()) {
				list = new ArrayList<>();
				do {
					list.add(rs.getString(1));
				} while(rs.next());
			}
		} catch(SQLException | NamingException e) {
			e.printStackTrace();

		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	
	public List<ServiceVO> getServiceList(String type, String local, String kind) {
		String Sql = "SELECT * FROM service WHERE type=? AND local=? AND kind=?";
		List<ServiceVO> list = null;

		try{
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(Sql);

			pstmt.setString(1, type);
			pstmt.setString(2, local);
			pstmt.setString(3, kind);
			
			rs = pstmt.executeQuery();

			if(rs.next()) {
				list = new ArrayList<>();
				do {
					ServiceVO s = new ServiceVO();

					s.setSno(rs.getInt(1));
					s.setFno(rs.getInt(2));
					s.setType(rs.getString(3));
					s.setKind(rs.getString(4));
					s.setLocal(rs.getString(5));
					s.setTitle(rs.getString(6));
					s.setImg(rs.getString(7));
					s.setPhone(rs.getString(8));
					s.setFee(rs.getInt(9));
					s.setDetail(rs.getString(10));
					s.setRegDate(rs.getTimestamp(11));
					s.setRtsDate(rs.getTimestamp(12));
					s.setRteDate(rs.getTimestamp(13));
					s.setRsDate(rs.getTimestamp(14));
					s.setReDate(rs.getTimestamp(15));
					s.setRcDate(rs.getInt(16));
					s.setStat(rs.getInt(17));
					s.setMaxrv(rs.getInt(18));
					s.setStartpt(rs.getInt(19));

					list.add(s);
				} while(rs.next());
			}
		} catch(SQLException | NamingException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return list;
	}
}